package com.cloud.force.api.service.impl;

import java.util.*;
import java.util.stream.Collectors;

import com.cloud.force.api.entity.*;
import com.cloud.force.common.result.SelectItem;
import com.cloud.force.interfaces.FeignClientMedia;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.cloud.force.api.bean.HumanDetail;
import com.cloud.force.api.bean.RecForceForm;
import com.cloud.force.api.bean.RecForceSearchBean;
import com.cloud.force.api.bean.RecForceSearchCriteria;
import com.cloud.force.api.bean.RecForceStatCriteria;
import com.cloud.force.api.mapper.RecCauseMapper;
import com.cloud.force.api.mapper.RecForceExecuteMapper;
import com.cloud.force.api.mapper.RecForceMapper;
import com.cloud.force.api.mapper.RecForceMeasureMapper;
import com.cloud.force.api.mapper.RecLitigantInfoMapper;
import com.cloud.force.api.mapper.RecTaskNumSeqMapper;
import com.cloud.force.api.service.RecForceService;
import com.cloud.force.common.consts.ForceConsts;
import com.cloud.force.common.context.HumanSession;
import com.cloud.force.common.utils.DateHelper;
import com.cloud.force.common.utils.RedisKeyHelper;
import com.cloud.force.common.utils.SessionHelper;
import com.cloud.force.common.utils.SqlConditionParserHelper;
import com.cloud.force.interfaces.FeignClientSystem;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;

import tk.mybatis.mapper.entity.Example;
import tk.mybatis.mapper.entity.Example.Criteria;

@Service
@Transactional
public class RecForceServiceImpl implements RecForceService {

	@Autowired
	private RecForceMapper forceMapper;

	@Autowired
	private RecForceMeasureMapper forceMeasureMapper;

	@Autowired
	private RecForceExecuteMapper forceExecuteMapper;

	@Autowired
	private RecCauseMapper recCauseMapper;

	@Autowired
	private RecLitigantInfoMapper recLitigantInfoMapper;

	@Autowired
	private RecTaskNumSeqMapper taskNumSeqMapper;

	@Autowired
	private FeignClientMedia feignClientMedia;

	@Autowired
	private JdbcTemplate jdbcTemplate;

	@Autowired
	private FeignClientSystem feignClientSystem;

	private static final Integer SELECT_HOUR = 1;
	private static final Integer SELECT_DAY = 2;
	private static final Integer SELECT_WEEK= 3;
	private static final Integer SELECT_MONTH = 4;
	private static final Integer SELECT_QUARTER = 5;
	private static final Integer SELECT_HALFYAER = 6;
	private static final Integer SELECT_YAER = 7;

	/** 默认文件路径 **/
	private static final String DEFAULT_MEIDA_ROOT = "DEFAULT_MEDIA_WEB_PATH_CONFIG";

	private static final long MEDIA_SYS_ID = 6;

	@Override
	public RecForceForm saveUpdate(RecForceForm form) {
		HumanSession session = SessionHelper.get();
		RecForce force = form.getForce();
		List<SelectItem> itemList = feignClientSystem.loadUnitDomainsByUnitId(session.getUnitId());
		if (null == force.getRecId() || 0 == force.getRecId()) {
			HumanDetail detail = feignClientSystem.getHumanDetail(SessionHelper.get().getHumanId());
			// 保存案卷信息
			force.setTaskNum(this.calculate(force));
			force.setCreateHumanId(session.getHumanId());
			force.setCreateHumanName(session.getHumanName());
			force.setUnitId(session.getUnitId());
			force.setUnitName(session.getUnitName());
			force.setUnitCode(session.getUnitCode());
			force.setCreateTime(new Date());
			force.setRegionCode(detail.getRegionCode());
			force.setSubjectId(detail.getSubjectId());
			force.setSubjectName(detail.getSubjectName());
			force.setStateId(ForceConsts.REC_STATE_NORMAL);
			force.setStateName(ForceConsts.REC_STATE_NAME_NORMAL);
			if (itemList.size() > 0){
				List<String> idList = itemList.stream().filter(vo-> vo.getValue()!=null).map(vo->vo.getValue().toString()).collect(Collectors.toList());
				List<String> nameList = itemList.stream().filter(vo-> StringUtils.isNotBlank(vo.getLabel())).map(vo->vo.getLabel()).collect(Collectors.toList());
				force.setDomainTypeId(StringUtils.join(idList,","));
				force.setDomainTypeName(StringUtils.join(nameList,","));
			}
			forceMapper.insert(force);
			Long recId = force.getRecId();
			RecForceMeasure measure = form.getForceMeasure();
			if (null != measure) {
				measure.setRecId(recId);
				forceMeasureMapper.insert(measure);
			}
			RecForceExecute execute = form.getForceExecute();
			;
			if (null != execute) {
				execute.setRecId(recId);
				forceExecuteMapper.insert(execute);
			}
			this.saveForceDetailInfo(form);
		} else {
			// 删除当时人信息
			Example litigantExample = new Example(RecLitigantInfo.class);
			Criteria litigantCriteria = litigantExample.createCriteria();
			litigantCriteria.andEqualTo("recId", force.getRecId());
			recLitigantInfoMapper.deleteByExample(litigantExample);
			// 删除案由信息
			Example causeExample = new Example(RecCause.class);
			Criteria causeCriteria = causeExample.createCriteria();
			causeCriteria.andEqualTo("recId", force.getRecId());
			recCauseMapper.deleteByExample(causeExample);

			force.setUnitCode(session.getUnitCode());
			force.setCreateTime(forceMapper.selectByPrimaryKey(force.getRecId()).getCreateTime());
			force.setUpdateTime(new Date());
			if (itemList.size() > 0){
				List<String> idList = itemList.stream().filter(vo-> vo.getValue()!=null).map(vo->vo.getValue().toString()).collect(Collectors.toList());
				List<String> nameList = itemList.stream().filter(vo-> StringUtils.isNotBlank(vo.getLabel())).map(vo->vo.getLabel()).collect(Collectors.toList());
				force.setDomainTypeId(StringUtils.join(idList,","));
				force.setDomainTypeName(StringUtils.join(nameList,","));
			}

			forceMapper.updateByPrimaryKey(force);
			RecForceMeasure measure = form.getForceMeasure();
			if (null != measure) {
				forceMeasureMapper.updateByPrimaryKey(measure);
			}
			RecForceExecute execute = form.getForceExecute();
			;
			if (null != execute) {
				forceExecuteMapper.updateByPrimaryKey(execute);
			}
			this.saveForceDetailInfo(form);
		}
		return form;
	}

	private String calculate(RecForce force) {
		Date now = new Date();
		StringBuilder sb = new StringBuilder(DateHelper.dateToFormatStr(now, DateHelper.DATE_FORMAT_YMD));
		sb.append(ForceConsts.BIZ_ID); // 行政强制
		sb.append(force.getRecTypeId());
		RecTaskNumSeq seq = taskNumSeqMapper.selectByPrimaryKey(force.getRecTypeId());
		int seqNum = 1;
		if (null != seq) {
			if (seq.getSeqDate().compareTo(DateHelper.getDateBegin(now)) > 0) {
				seqNum = seq.getSeqValue();
			}
			seq.setSeqValue(seqNum + 1);
			seq.setSeqDate(now);
			taskNumSeqMapper.updateByPrimaryKey(seq);
		} else {
			seq = new RecTaskNumSeq();
			seq.setSeqId(force.getRecTypeId());
			seq.setSeqDate(new Date());
			seq.setSeqValue(seqNum + 1);
			taskNumSeqMapper.insert(seq);
		}
		sb.append(String.format("%05d", seqNum));
		return sb.toString();
	}

	private void saveForceDetailInfo(RecForceForm form) {
		// 保存当事人信息
		List<RecLitigantInfo> infos = form.getLitigants();
		if (null != infos && infos.size() > 0) {
			for (RecLitigantInfo info : infos) {
				info.setRecId(form.getForce().getRecId());
				if ("01".equals(info.getLitigantTypeId())) {
					info.setCompanyAddress(null);
					info.setCompanyLegalPerson(null);
					info.setCompanyName(null);
					info.setCompanyPosition(null);
					info.setCompanySocialNum(null);
				} else {
					info.setPersonAddress(null);
					info.setPersonIdcardName(null);
					info.setPersonIdcardType(null);
					info.setPersonIdcardNum(null);
					info.setPersonName(null);
					info.setPersonAddress(null);
				}
				recLitigantInfoMapper.insert(info);
			}
		}

		// 保存案由相关信息
		if (null != form.getCauses() && form.getCauses().size() > 0) {
			for (RecCause cause : form.getCauses()) {
				cause.setRecId(form.getForce().getRecId());
				recCauseMapper.insert(cause);
			}
		}
	}

	@Override
	public RecForceForm load(long recId) {
		RecForceForm form = new RecForceForm();
		RecForce force = forceMapper.selectByPrimaryKey(recId);
		String url = feignClientSystem.getMediaRootPath(MEDIA_SYS_ID,DEFAULT_MEIDA_ROOT);
		form.setForce(force);
		if ("01".equals(force.getRecTypeId())) {
			RecForceMeasure measure = forceMeasureMapper.selectByPrimaryKey(recId);
			measure.setDecideDocUrl(getUrl(measure.getDecideDocId(),url));
			form.setForceMeasure(measure);
		} else {
			RecForceExecute execute = forceExecuteMapper.selectByPrimaryKey(recId);
			form.setForceExecute(execute);
		}

		Example litigantExample = new Example(RecLitigantInfo.class);
		// 当事人
		Example.Criteria litigantCriteria = litigantExample.createCriteria();
		litigantCriteria.andEqualTo("recId", recId);
		List<RecLitigantInfo> litigants = recLitigantInfoMapper.selectByExample(litigantExample);
		form.setLitigants(litigants);
		// 案由
		Example causeExample = new Example(RecCause.class);
		Example.Criteria causeCriteria = causeExample.createCriteria();
		causeCriteria.andEqualTo("recId", recId);
		List<RecCause> causes = recCauseMapper.selectByExample(causeExample);
		form.setCauses(causes);
		return form;
	}

	@Override
	public PageInfo<RecForceSearchBean> searchForcePage(RecForceSearchCriteria criteria) {
		return PageHelper.startPage(criteria.getPageNum(), criteria.getPageSize())
				.doSelectPageInfo(() -> forceMapper.searchForcePage(criteria));
	}

	@Override
	public PageInfo<Map<String, Object>> statForcePage(RecForceStatCriteria criteria) {
		StringBuilder sqlBuilder = null;
		if(1 == criteria.getId()){
			sqlBuilder = new StringBuilder("select unit_name, sum(las) as las, sum(fine) as fine, sum(qzcs) as qzcs, sum(qzzx) AS qzzx, sum(xzzy) as xzzy, sum(cfcs) as cfcs, sum(kycw) as kycw, sum(djck) as djck, sum(qt) as qt, ")
					.append("sum(jcfj) as jcfj, sum(hbck) as hbck, sum(pmcf) as pmcf, sum(pcfa) as pcfa, sum(dlx) as dlx, sum(zxqt) as zxqt from (");
		}else{
			sqlBuilder = new StringBuilder("select domain_type_name, sum(las) as las, sum(fine) as fine, sum(qzcs) as qzcs, sum(qzzx) AS qzzx, sum(xzzy) as xzzy, sum(cfcs) as cfcs, sum(kycw) as kycw, sum(djck) as djck, sum(qt) as qt, ")
					.append("sum(jcfj) as jcfj, sum(hbck) as hbck, sum(pmcf) as pmcf, sum(pcfa) as pcfa, sum(dlx) as dlx, sum(zxqt) as zxqt from (");
		}
		sqlBuilder.append("select r1.unit_id, r1.unit_name, r1.domain_type_id, 1 as las, r1.domain_type_name, if(r3.increase_fine is null, 0, r3.increase_fine) fine, ")
			.append("case when r1.rec_type_id = '01' then 1 else 0 end as qzcs,")
			.append("case when r1.rec_type_id = '02' then 1 else 0 end as qzzx,")
			
			.append("case when r1.rec_type_id = '01' and r2.measure_type_id = '01' then 1 else 0 end as xzzy,")
			.append("case when r1.rec_type_id = '01' and r2.measure_type_id = '02' then 1 else 0 end as cfcs,")
			.append("case when r1.rec_type_id = '01' and r2.measure_type_id = '03' then 1 else 0 end as kycw,")
			.append("case when r1.rec_type_id = '01' and r2.measure_type_id = '04' then 1 else 0 end as djck,")
			.append("case when r1.rec_type_id = '01' and r2.measure_type_id = '10' then 1 else 0 end as qt,")
			
			.append("case when r1.rec_type_id = '02' and r3.execute_type_id = '01' then 1 else 0 end as jcfj,")
			.append("case when r1.rec_type_id = '02' and r3.execute_type_id = '02' then 1 else 0 end as hbck,")
			.append("case when r1.rec_type_id = '02' and r3.execute_type_id = '03' then 1 else 0 end as pmcf,")
			.append("case when r1.rec_type_id = '02' and r3.execute_type_id = '04' then 1 else 0 end as pcfa,")
			.append("case when r1.rec_type_id = '02' and r3.execute_type_id = '05' then 1 else 0 end as dlx,")
			.append("case when r1.rec_type_id = '02' and r3.execute_type_id = '10' then 1 else 0 end as zxqt ")
			.append("from rec_force r1 left join rec_force_measure r2 on r1.rec_id = r2.rec_id ")
			.append("left join rec_force_execute r3 on r1.rec_id = r3.rec_id where r1.state_id <> 99");
			
		// 区划过滤，只能查看平级或下级区划的案卷信息
		if(!StringUtils.isEmpty(criteria.getRegionCode())){
			sqlBuilder.append(" and r1.region_code like '"+ criteria.getRegionCode() +"%' "); 
		}
		// 领域过滤只能查看管辖领域内的案卷
		if(null != criteria.getDomains() && criteria.getDomains().size() > 0){
			sqlBuilder.append(" and r1.domain_type_id in ("+ StringUtils.join(criteria.getDomains(), ",")+") ");
		}
		String conditions = SqlConditionParserHelper.buildConditionSql(criteria.getConds());
		if(!StringUtils.isEmpty(conditions)){
			sqlBuilder.append(conditions);
		}
		if(1 == criteria.getId()){
			sqlBuilder.append(" ) t group by unit_id");
		}else{
			sqlBuilder.append(" ) t group by domain_type_id");
		}
		final String sql = sqlBuilder.toString();
		return PageHelper.startPage(criteria.getPageNum(), criteria.getPageSize())
				.doSelectPageInfo(() -> forceMapper.selectByDynamicSQL(sql));
	}

	@Override
	public void cancel(Long recId) {
		jdbcTemplate.update("update rec_force set state_id = ?, state_name = ? where rec_id = ?",
				ForceConsts.REC_STATE_CANCEL, ForceConsts.REC_STATE_NAME_CANCEL, recId);
	}

	@Override
	public List<Map<String, Object>> getRecForceByDate(String start, String end, String unitCode, Integer type, Integer recType) {
		List<Map<String, Object>> queryForList = new ArrayList<>();
		start = DateHelper.getFirstYearDay(start);
		end =  DateHelper.getEndDay(end);
		// 01 强制措施  02 强制执行
		String unitTypeFilter = "";
		if (recType != null && recType == 1) {
			unitTypeFilter = " and rec_type_id = '01' ";
		}else if  (recType != null && recType == 2){
			unitTypeFilter = " and rec_type_id =  '02'  ";
		}
		if (type.equals(SELECT_HOUR)){
			String sql = "SELECT count(1) AS `count`,DATE_FORMAT(DATE_ADD(\tCONCAT(DATE_FORMAT(create_time, '%Y-%m-%d %H:'),\n" +
					"(FLOOR(DATE_FORMAT(create_time, '%i') / 60))*60,':00'), INTERVAL 1 HOUR),'%H') as  timeRange\n" +
					"from rec_force " +
					"where create_time >= '"+start+"' " +
					"and create_time <= '"+end+"' " +
					"AND unit_code LIKE '"+unitCode+"%' "+unitTypeFilter+" AND  state_id <> 99  group by  timeRange;";
			queryForList = jdbcTemplate.queryForList(sql);
		}else if (type.equals(SELECT_DAY)){
			String sql = " SELECT COUNT(1) AS `count`, DATE_FORMAT(create_time,'%m-%d') AS  timeRange FROM rec_force where create_time >= '"+start+"'  " +
					"and create_time <= '"+end+"' AND unit_code LIKE '"+unitCode+"%' "+unitTypeFilter+" AND  state_id <> 99 group by  timeRange ORDER BY  timeRange ASC ;";
			queryForList = jdbcTemplate.queryForList(sql);
		}else if (type.equals(SELECT_WEEK)){
			String sql = " SELECT COUNT(1) AS  	`count`, DATE_FORMAT(create_time,'%Y-%m-%d') AS  timeRange FROM rec_force  where create_time >= '"+start+"'  " +
					"and create_time <= '"+end+"' AND unit_code LIKE '"+unitCode+"%' "+unitTypeFilter+" GROUP BY  timeRange  ORDER BY  timeRange ASC;";
			queryForList = jdbcTemplate.queryForList(sql);
		}else if (type.equals(SELECT_MONTH) || type.equals(SELECT_QUARTER) || type.equals(SELECT_HALFYAER)){
			String sql = "SELECT COUNT(1) AS `count`, DATE_FORMAT(create_time,'%Y-%m') AS  timeRange FROM rec_force where create_time >= '"+start+"'  " +
					"and create_time <= '"+end+"' AND unit_code LIKE '"+unitCode+"%' "+unitTypeFilter+" AND  state_id <> 99 GROUP BY  timeRange  ORDER BY  timeRange ASC;";
			queryForList = jdbcTemplate.queryForList(sql);
		}else if (type.equals(SELECT_YAER)){
			String sql = "SELECT COUNT(1) AS `count`, DATE_FORMAT(create_time,'%Y') AS  timeRange FROM rec_force where create_time >= '"+start+"'  " +
					"and create_time <= '"+end+"' AND unit_code LIKE '"+unitCode+"%' "+unitTypeFilter+" AND  state_id <> 99 GROUP BY  timeRange  ORDER BY  timeRange ASC;";
			queryForList = jdbcTemplate.queryForList(sql);
		}
		// 根据类型判断返回分时、日、周 月
		return queryForList;
	}

	@Override
	public List<Map<String, Object>> getLegalCaseSource(String start, String end, String unitCode,Integer recType) {
		start = DateHelper.getFirstYearDay(start);
		end =  DateHelper.getEndDay(end);
		// 01 强制措施  02 强制执行
		String unitTypeFilter = "";
		if (recType != null && recType == 1) {
			unitTypeFilter = " and rec_type_id = '01' ";
		}else if (recType != null && recType == 2){
			unitTypeFilter = " and rec_type_id =  '02'  ";
		}
		String sql = " SELECT b.count,a.timeRange FROM (SELECT event_src_id, event_src_name AS timeRange FROM rec_force WHERE  state_id <> 99 GROUP BY event_src_name)a" +
				" left join ( SELECT count(1)AS `count`,event_src_name AS  timeRange FROM rec_force where create_time >= '"+start+"'  " +
				"and create_time <= '"+end+"' AND unit_code LIKE '"+unitCode+"%' "+unitTypeFilter+"  AND  state_id <> 99 group by  event_src_name)b  ON a.timeRange = b.timeRange";
		List<Map<String, Object>> queryForList = jdbcTemplate.queryForList(sql);
		return queryForList;
	}

	@Override
	public List<Map<String, Object>> getLegalCaseRegion(String start, String end, String unitCode,String regionCode, Integer recType) {
		start = DateHelper.getFirstYearDay(start);
		end =  DateHelper.getEndDay(end);
		// 01 强制措施  02 强制执行
		String unitTypeFilter = "";
		if (recType != null && recType == 1) {
			unitTypeFilter = " and rec_type_id = '01' ";
		}else if  (recType != null && recType == 2){
			unitTypeFilter = " and rec_type_id =  '02'  ";
		}
		String unitCodeFilter = "";
		if (!unitCode .equals("/1/")){
			unitCodeFilter = " region_code";
		}else{
			unitCodeFilter = " LEFT (region_code, 4) AS region_code";
		}
		String doMainSql = "SELECT region_code,count(*) AS `count` FROM(SELECT  "+unitCodeFilter+" FROM rec_force where create_time >= '"+start+"'  " +
				"and create_time <= '"+end+"' AND unit_code LIKE '"+unitCode+"%' "+unitTypeFilter+" AND region_code != "+regionCode+"  AND  state_id <> 99) t GROUP BY region_code;  ";
		return jdbcTemplate.queryForList(doMainSql);
	}

	@Override
	public List<Map<String, Object>> getLegalCaseDomain(String start, String end, String unitCode, Integer recType, Integer num) {
		start = DateHelper.getFirstYearDay(start);
		end =  DateHelper.getEndDay(end);
		// 01 强制措施  02 强制执行
		String unitTypeFilter = "";
		if (recType != null && recType == 1) {
			unitTypeFilter = " and rec_type_id = '01' ";
		}else if  (recType != null && recType == 2){
			unitTypeFilter = " and rec_type_id =  '02'  ";
		}
		String doMainSql = "SELECT count(1) AS `count`, domain_type_name AS timeRange FROM rec_force where domain_type_name is not null and create_time >= '"+start+"'  " +
				"and create_time <= '"+end+"' AND unit_code LIKE '"+unitCode+"%' "+unitTypeFilter+"  AND  state_id <> 99 GROUP  BY domain_type_id LIMIT 0," + num;;
		return jdbcTemplate.queryForList(doMainSql);
	}

	@Override
	public Map<String, Object> getLegalCaseGeneral(String start, String end , String unitCode) {
		start = DateHelper.getFirstYearDay(start);
		end =  DateHelper.getEndDay(end);
		String doMainSql = "SELECT * FROM (SELECT count(*) AS `force` FROM rec_force where create_time >= '"+start+"'  "+ "and create_time <= '"+end+"' AND unit_code LIKE '"+unitCode+"%'AND  state_id <> 99 )a," +
				"(SELECT count(*) AS simple FROM rec_force WHERE rec_type_id = '01' AND create_time >= '"+start+"'  "+ "and create_time <= '"+end+"' AND unit_code LIKE '"+unitCode+"%' AND  state_id <> 99)b," +
				"(SELECT count(*) AS normal  FROM rec_force WHERE rec_type_id = '02' AND create_time >= '"+start+"'  "+ "and create_time <= '"+end+"' AND unit_code LIKE '"+unitCode+"%'AND  state_id <> 99 ) c ";
		List<Map<String,Object>> list = jdbcTemplate.queryForList(doMainSql);
		Map<String,Object> map = new HashMap<>();
		if (list!= null){
			list.forEach(map::putAll);
		}
		return map;
	}

	@Override
	public List<Map<String, Object>> getLawCaseTop5(String start, String end, String unitCode, Integer recType) {
		start = DateHelper.getFirstYearDay(start);
		end =  DateHelper.getEndDay(end);
		// 01 强制措施  02 强制执行
		String unitTypeFilter = "";
		if (recType != null && recType == 1) {
			unitTypeFilter = " and rp.rec_type_id = '01' ";
		}else if  (recType != null && recType == 2){
			unitTypeFilter = " and rp.rec_type_id =  '02'  ";
		}
		String doMainSql = "SELECT re.law_title AS timeRange,count(1) AS `count`FROM rec_cause re LEFT JOIN rec_force rp ON re.rec_id = rp.rec_id WHERE re.law_title IS NOT NULL " +
				""+unitTypeFilter+" AND rp.create_time >= '"+start+"' and rp.create_time <= '"+end+"' AND rp.unit_code LIKE '"+unitCode+"%' AND  state_id <> 99 GROUP BY re.law_title ORDER BY `count` DESC LIMIT 0,5";
		return jdbcTemplate.queryForList(doMainSql);
	}

	@Override
	public List<Map<String, Object>> getLawCaseTop(String start, String end, String unitCode, Integer num) {
		start = DateHelper.getFirstYearDay(start);
		end = DateHelper.getEndDay(end);

		String Sql = "SELECT re.law_code AS lawcode, re.law_title AS lawtitle, count(1) AS `count` FROM rec_cause re LEFT JOIN rec_force rp ON re.rec_id = rp.rec_id WHERE re.law_code IS NOT NULL " +
				" AND rp.create_time >= '" + start + "' and rp.create_time <= '" + end + "' AND rp.unit_code LIKE '" + unitCode + "%' AND  state_id <> 99 GROUP BY re.law_code, re.law_title ORDER BY `count` DESC";
		if (num == null) {
			num = 10;
		}
		if (num > 0) {
			Sql += " LIMIT 0," + num;
		}
		List<Map<String, Object>> list = jdbcTemplate.queryForList(Sql);
		return list;
	}

	@Override
	public List<Map<String, Object>> getBasisCodeByRegionCode(String start, String end, String unitCode, Long regionid) {
		start = DateHelper.getFirstYearDay(start);
		end = DateHelper.getEndDay(end);
		String regionFilter = "LEFT (region_code, 4) AS regionCode";
		if (regionid != null && regionid != 1) {
			regionFilter = "region_code as regionCode";
		}
		String doMainSql = "SELECT "+regionFilter+", rec.infringe_basis_code FROM rec_force rep " +
				"LEFT JOIN rec_cause rec ON rep.rec_id = rec.rec_id WHERE rep.unit_code LIKE '" + unitCode + "%' AND rep.region_code <> '45' AND  rec.infringe_basis_code IS NOT NULL AND create_time >= '"+start+"'  "+ "and create_time <= '"+end+"' AND  state_id <> 99 ORDER BY rep.region_code  DESC ";
		return jdbcTemplate.queryForList(doMainSql);
	}

	@Override
	public List<Map<String, Object>> getLawUseStat(String start, String end, String unitCode) {
		start = DateHelper.getFirstYearDay(start);
		end = DateHelper.getEndDay(end);

		String Sql = "SELECT re.law_code AS lawcode, re.law_title AS lawtitle, count(1) AS `count` FROM rec_cause re LEFT JOIN rec_force rp ON re.rec_id = rp.rec_id WHERE re.law_code IS NOT NULL " +
				" AND rp.create_time >= '" + start + "' and rp.create_time <= '" + end + "' AND rp.unit_code LIKE '" + unitCode + "%' AND  rp.state_id <> 99  GROUP BY re.law_code, re.law_title ORDER BY `count` DESC";
		List<Map<String, Object>> list = jdbcTemplate.queryForList(Sql);
		return list;
	}

    @Override
    public List<Map<String, Object>> getCauseUseStat(String start, String end, String unitCode, String lawID) {
		start = DateHelper.getFirstYearDay(start);
		end = DateHelper.getEndDay(end);

		String Sql = "SELECT re.cause_code AS lawcode, re.cause_content AS lawtitle, count(1) AS `count` FROM rec_cause re LEFT JOIN rec_force rp ON re.rec_id = rp.rec_id WHERE re.law_code IS NOT NULL " +
				" AND rp.create_time >= '" + start + "' and rp.create_time <= '" + end + "' AND rp. unit_code LIKE '" + unitCode + "%' AND re.law_code = '"+lawID+"' AND  rp.state_id <> 99 GROUP BY re.cause_code, re.cause_code ORDER BY `count` DESC";
		List<Map<String, Object>> list = jdbcTemplate.queryForList(Sql);
		return list;
    }

	@Override
	public Map<String, Object> getLawDynamicsInfo(String unitCode) {
		String doMainSql = "SELECT count(*) AS `force` FROM rec_force where unit_code LIKE '"+unitCode+"%' AND  state_id <> 99";
		List<Map<String,Object>> list = jdbcTemplate.queryForList(doMainSql);
		Map<String,Object> map = new HashMap<>();
		if (list!= null){
			list.forEach(map::putAll);
		}
		return map;
	}

	@Override
	public List<Map<String, Object>> getForceByRegionId(String start, String end, String unitCode,Integer recType) {
		 start = DateHelper.getFirstYearDay(start);
		 end = DateHelper.getEndDay(end);
		// 01 强制措施  02 强制执行
		String unitTypeFilter = "";
		if (recType != null && recType == 1) {
			unitTypeFilter = " and rec_type_id = '01' ";
		}else if  (recType != null && recType == 2){
			unitTypeFilter = " and rec_type_id =  '02'  ";
		}
		String sql = "SELECT UI.subject_name AS `name`,COUNT(1) AS `value` FROM forces.rec_force rec LEFT JOIN cda.unit_info UI ON rec.unit_id = UI.unit_id WHERE UI.unit_code LIKE '"+ unitCode + "%' AND rec.create_time >= '" + start + "' and rec.create_time <= '" + end + "'AND rec.state_id <> 99 "+unitTypeFilter+" GROUP BY UI.subject_id";
		return jdbcTemplate.queryForList(sql);
	}

	private List<Map<String,Object>> getUrl(String ids ,String url){
		List<Map<String,Object>> informUrl = new ArrayList<>();
		if (StringUtils.isNotBlank(ids)){
			String[] inform = ids.trim().split("/");
			List<Media> informList = feignClientMedia.getAllMediaByIds(Arrays.asList(inform));
			if(informList != null){
				informList.forEach(j-> {
					Map<String, Object> map = new HashMap<>();
					map.put("name", j.getMediaName());
					map.put("url", url+j.getMediaPath());
					map.put("uid",j.getMediaId());
					informUrl.add(map);
				});
			}
		}
		return informUrl;
	}
	public String calculate(String recTypeId) {
		Date now = new Date();
		StringBuilder sb = new StringBuilder(DateHelper.dateToFormatStr(now, DateHelper.DATE_FORMAT_YMD));
		sb.append(ForceConsts.BIZ_ID); // 行政强制
		sb.append(recTypeId);
		RecTaskNumSeq seq = taskNumSeqMapper.selectByPrimaryKey(recTypeId);
		int seqNum = 1;
		if (null != seq) {
			if (seq.getSeqDate().compareTo(DateHelper.getDateBegin(now)) > 0) {
				seqNum = seq.getSeqValue();
			}
			seq.setSeqValue(seqNum + 1);
			seq.setSeqDate(now);
			taskNumSeqMapper.updateByPrimaryKey(seq);
		} else {
			seq = new RecTaskNumSeq();
			seq.setSeqId(recTypeId);
			seq.setSeqDate(new Date());
			seq.setSeqValue(seqNum + 1);
			taskNumSeqMapper.insert(seq);
		}
		sb.append(String.format("%05d", seqNum));
		return sb.toString();
	}
}
